package com.foreknow.elm.dao.Impl;

import com.foreknow.elm.dao.DeliveryAddressDao;
import com.foreknow.elm.po.DeliveryAddress;
import com.foreknow.elm.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DeliveryAddressDaoImpl implements DeliveryAddressDao {
    private Connection conn;
    private PreparedStatement pst;
    private ResultSet rs;
    @Override
    public List<DeliveryAddress> listDeliveryAddressByUserId(String userId) throws SQLException {
        List<DeliveryAddress> list=new ArrayList<>();
        String sql="select * from deliveryaddress where userId=?";
        try {
            conn=DBUtil.getConnection();
            pst=conn.prepareStatement(sql);
            pst.setString(1,userId);
            rs= pst.executeQuery();
            while (rs.next()){
                DeliveryAddress da=new DeliveryAddress();
                da.setDaId(rs.getInt("DaId"));
                da.setContactName(rs.getString("ContactName"));
                da.setContactSex(rs.getInt("ContactSex"));
                da.setContactTel(rs.getString("ContactTel"));
                da.setAddress(rs.getString("Address"));
                da.setUserId(rs.getString("UserId"));
                list.add(da);
            }
        }finally {
            DBUtil.close(pst);
        }
        return list;
    }

    @Override
    public DeliveryAddress getDeliveryAddressById(Integer daId) throws SQLException {
        DeliveryAddress da=new DeliveryAddress();
        String sql="select * from deliveryaddress where daId=?";
        try {
            conn=DBUtil.getConnection();
            pst=conn.prepareStatement(sql);
            pst.setInt(1,daId);
            rs= pst.executeQuery();
            while (rs.next()) {
                da.setDaId(rs.getInt("DaId"));
                da.setContactName(rs.getString("ContactName"));
                da.setContactSex(rs.getInt("ContactSex"));
                da.setContactTel(rs.getString("ContactTel"));
                da.setAddress(rs.getString("Address"));
                da.setUserId(rs.getString("UserId"));
            }
        }finally {
            DBUtil.close(pst);
        }
        return da;
    }

    @Override
    public int saveDeliveryAddress(String contactName, Integer contactSex, String contactTel, String address, String userId) throws SQLException {
        int i=0;
        String sql="insert into deliveryaddress(contactName,contactSex,contactTel,address,userId) value(?,?,?,?,?)";
        try {
            conn=DBUtil.getConnection();
            pst= conn.prepareStatement(sql);
            pst.setString(1,contactName);
            pst.setInt(2,contactSex);
            pst.setString(3,contactTel);
            pst.setString(4,address);
            pst.setString(5,userId);
            i=pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }
        return i;
    }

    @Override
    public int updateDeliveryAddress(Integer daId, String contactName, Integer contactSex, String contactTel, String address, String userId) throws SQLException {
        int i=0;
        String sql="update deliveryaddress set contactName=?,contactSex=?,contactTel=?,address=? where daId=? and userId=?";
        try {
            conn=DBUtil.getConnection();
            pst= conn.prepareStatement(sql);
            pst.setString(1,contactName);
            pst.setInt(2,contactSex);
            pst.setString(3,contactTel);
            pst.setString(4,address);
            pst.setInt(5,daId);
            pst.setString(6,userId);
            i=pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }
        return i;
    }

    @Override
    public int removeDeliveryAddress(Integer daId) throws SQLException {
        int i=0;
        String sql="delete from deliveryaddress where daId=?";
        try {
            conn=DBUtil.getConnection();
            pst= conn.prepareStatement(sql);
            pst.setInt(1,daId);
            i=pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }
        return i;
    }

    public static void main(String[] args) throws SQLException {
        DeliveryAddressDao dad=new DeliveryAddressDaoImpl();

//        System.out.println(dad.removeDeliveryAddress(16));

        System.out.println(dad.updateDeliveryAddress(19,"ABC12",1,"1415324324","nj","12345671111"));

//        System.out.println(dad.saveDeliveryAddress("tom",1,"12314","anshan","12344321"));

//        DeliveryAddress da=dad.getDeliveryAddressById(15);
//        System.out.println(da.getContactName());

//        List<DeliveryAddress>list= dad.listDeliveryAddressByUserId("11111111111");
//        for (int i=0;i< list.size();i++){
//            System.out.println(list.get(i).getContactName());
        }
    }

